﻿using Common;
using Newtonsoft.Json.Linq;
using System;
using System.CodeDom;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    public static class ModelSqlExtensions
    {
        /// <summary>
        /// 从实体中获取属性值不为空的属性和值，用于数据库Update
        /// 暂时只支持，int、string、double、DateTime
        /// 要求数值类型默认为-1
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static List<string> GetPropertyValueNotNullForUpdate(this object data, List<string> ignoreFields = null)
        {
            string item = "";
            List<string> items = new List<string>();

            PropertyInfo[] propertyInfos = data.GetType().GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (ignoreFields != null && ignoreFields.Contains(propertyInfo.Name.ToUpper()))
                    continue;

                object obj = propertyInfo.GetValue(data, null); 
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                        if (Convert.ToInt32(obj) == -1)
                            continue;
                        item = $"{propertyInfo.Name}={Convert.ToInt32(obj)}";
                        break;
                    case "System.String":
                        if (Convert.ToString(obj) == "")
                            continue;
                        item = $"{propertyInfo.Name}='{Convert.ToString(obj)}'";
                        break;
                    case "System.Double":
                        if (Convert.ToDouble(obj) == -1)
                            continue;
                        item = $"{propertyInfo.Name}={Convert.ToDouble(obj)}";
                        break;
                    case "System.Decimal":
                        if (Convert.ToDecimal(obj) == -1)
                            continue;
                        item = $"{propertyInfo.Name}={Convert.ToDecimal(obj)}";
                        break;
                    case "System.DateTime":
                        if (Convert.ToDateTime(obj) == DateTime.MinValue)
                            continue;
                        item = $"{propertyInfo.Name}='{Convert.ToDateTime(obj)}'";
                        break;
                    case "System.Boolean":
                        item = $"{propertyInfo.Name}='{Convert.ToInt32(obj)}'";
                        break;
                }
                if (!CheckAttrSqlIgnore(propertyInfo, obj, ref item))
                    continue;
                items.Add(item);
            }
            return items;
        }



        /// <summary>
        /// 从实体中获取属性值不为空的属性和值，用于数据库Insert
        /// 暂时只支持，int、string、double、DateTime
        /// 要求数值类型默认为-1
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetPropertyValueNotNullForInsert(this object data, List<string> ignoreFields = null)
        {
            string value = "";
            Dictionary<string, string> items = new Dictionary<string, string>();

            PropertyInfo[] propertyInfos = data.GetType().GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (ignoreFields != null && ignoreFields.Contains(propertyInfo.Name.ToUpper()))
                    continue;

                object obj = propertyInfo.GetValue(data, null);
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                        if (Convert.ToInt32(obj) == -1)
                            continue;
                        value = Convert.ToInt32(obj).ToString();
                        break;
                    case "System.String":
                        if (Convert.ToString(obj) == "")
                            continue;
                        value = $"'{Convert.ToString(obj)}'";
                        break;
                    case "System.Double":
                        if (Convert.ToDouble(obj) == -1)
                            continue;
                        value = Convert.ToDouble(obj).ToString();
                        break;
                    case "System.Decimal":
                        if (Convert.ToDecimal(obj) == -1)
                            continue;
                        value = Convert.ToDecimal(obj).ToString();
                        break;
                    case "System.DateTime":
                        if (Convert.ToDateTime(obj) == DateTime.MinValue)
                            continue;
                        value = $"'{Convert.ToDateTime(obj)}'";
                        break;
                    case "System.Boolean":
                        value = $"'{Convert.ToInt32(obj)}'";
                        break;
                }
                if (!CheckAttrSqlIgnore(propertyInfo, obj, ref value))
                    continue;

                items.Add(propertyInfo.Name, value);
            }
            return items;
        }



        /// <summary>
        /// 从实体中获取属性值不为空的属性和值，用于数据库Select的Where条件
        /// 暂时只支持，int、string、double、DateTime
        /// 要求数值类型默认为-1
        /// </summary>
        /// <param name="data"></param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public static List<string> GetPropertyValueNotNullForSelectWhere(this object data, string prefix, List<string> ignoreFields = null)
        {
            string item = "";
            List<string> items = new List<string>();
            Dictionary<string, SqlBetweenAttribute> dic = new Dictionary<string, SqlBetweenAttribute>();

            PropertyInfo[] propertyInfos = data.GetType().GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (ignoreFields != null && ignoreFields.Contains(propertyInfo.Name.ToUpper()))
                    continue;

                item = "";
                object obj = propertyInfo.GetValue(data, null);
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                        if (Convert.ToInt32(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToInt32(obj)}";
                        break;
                    case "System.String":
                        if (Convert.ToString(obj) == "")
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}='{Convert.ToString(obj)}'";
                        break;
                    case "System.Double":
                        if (Convert.ToDouble(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToDouble(obj)}";
                        break;
                    case "System.Decimal":
                        if (Convert.ToDecimal(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToDecimal(obj)}";
                        break;
                    case "System.DateTime":
                        if (Convert.ToDateTime(obj) == DateTime.MinValue)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}='{Convert.ToDateTime(obj)}'";
                        break;
                    case "System.Boolean":
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToInt32(obj)}";
                        break;
                }

                CheckAttrSqlRange(propertyInfo, obj, ref item, prefix);
                CheckAttrSqlLike(propertyInfo, obj, ref item, prefix);
                if (!CheckAttrSqlIgnore(propertyInfo, obj, ref item))
                    continue;

                items.Add(item);
            }
            return items;
        }

        #region Attribute

        /// <summary>
        /// 检查属性是否被SqlBetween特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo">实体的属性对象</param>
        /// <param name="obj">属性的值</param>
        /// <param name="dic">暂存特性的字典</param>
        /// <param name="item"></param>
        /// <returns>true 表示需要把item加到List<string>中</returns>
        static bool CheckAttrSqlBetween(PropertyInfo propertyInfo, object obj, ref Dictionary<string, SqlBetweenAttribute> dic, ref string item)
        {
            SqlBetweenAttribute attr = (SqlBetweenAttribute)propertyInfo.GetCustomAttribute(typeof(SqlBetweenAttribute), false);
            if (attr == null)
                return true;

            attr.Value = obj;
            if (!dic.ContainsKey(attr.AnotherFieldName))
            {   //缺少另外一个，先缓存
                dic.Add(attr.AnotherFieldName, attr);
                return false;
            }
            else
            {
                SqlBetweenAttribute _attr = dic[attr.AnotherFieldName];
                dic.Remove(attr.AnotherFieldName);

                SqlBetweenAttribute attrb = attr.IsStart ? attr : _attr;
                SqlBetweenAttribute attre = attr.IsStart ? _attr : attr;
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                    case "System.Double":
                    case "System.Decimal":
                        item = $" and {attr.FieldName} between {attrb.Value} and {attre.Value}";
                        break;
                    case "System.String":
                    case "System.DateTime":
                        item = $" and {attr.FieldName} between '{attrb.Value}' and '{attre.Value}'";
                        break;
                }
                return true;
            }
        }

        /// <summary>
        /// 检查属性是否被SqlRange特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        static void CheckAttrSqlRange(PropertyInfo propertyInfo, object obj, ref string item, string prefix)
        {
            SqlRangeAttribute attr = (SqlRangeAttribute)propertyInfo.GetCustomAttribute(typeof(SqlRangeAttribute), false);
            if (attr == null)
                return;

            switch (propertyInfo.PropertyType.FullName)
            {
                case "System.Int32":
                case "System.Double":
                case "System.Decimal":
                    item = $" and {prefix}{attr.FieldName} {attr.Range} {obj} ";
                    break;
                case "System.String":
                case "System.DateTime":
                    item = $" and {prefix}{attr.FieldName} {attr.Range} '{obj}' ";
                    break;
            }
            return;
        }

        /// <summary>
        /// 检查属性是否被SqlLike特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        static void CheckAttrSqlLike(PropertyInfo propertyInfo, object obj, ref string item, string prefix)
        {
            SqlLikeAttribute attr = (SqlLikeAttribute)propertyInfo.GetCustomAttribute(typeof(SqlLikeAttribute), false);
            if (attr == null)
                return;

            switch (propertyInfo.PropertyType.FullName)
            {
                case "System.String":
                    item = $" and ({prefix}{attr.FieldName} like '%{obj}%' or {prefix}{attr.FieldName}='{obj}') ";
                    break;
            }
            return;
        }

        /// <summary>
        /// 检查属性是否被SqlIgnoreAttribute特性修饰，如果修饰则不加入到Where条件中
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        static bool CheckAttrSqlIgnore(PropertyInfo propertyInfo, object obj, ref string item)
        {
            SqlIgnoreAttribute attr = (SqlIgnoreAttribute)propertyInfo.GetCustomAttribute(typeof(SqlIgnoreAttribute), false);
            if (attr == null)
                return true;
            else
                return false;
        }

        #endregion



        /// <summary>
        /// 从实体列表中获取所有属性名称和值，用于数据库批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="fields">属性名列表</param>
        /// <param name="ignoreFields">要排除的属性列表</param>
        /// <returns></returns>
        public static List<List<string>> GetPropertyValueForBatchInsert<T>(this List<T> data, out List<string> fields, List<string> ignoreFields = null)
        {
            fields = new List<string>();
            List<List<string>> items = new List<List<string>>();

            string value = "";
            PropertyInfo[] propertyInfos = data[0].GetType().GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (ignoreFields != null && ignoreFields.Contains(propertyInfo.Name.ToUpper()))
                    continue;
                if (!CheckAttrSqlIgnore(propertyInfo, data[0], ref value))
                    continue;
                fields.Add(propertyInfo.Name);
            }

            foreach (T t in data)
            {
                List<string> values = new List<string>();
                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    if (ignoreFields != null && ignoreFields.Contains(propertyInfo.Name.ToUpper()))
                        continue;

                    object obj = propertyInfo.GetValue(t, null);
                    switch (propertyInfo.PropertyType.FullName)
                    {
                        case "System.Int32":
                            value = Convert.ToInt32(obj).ToString();
                            break;
                        case "System.String":
                            value = $"'{Convert.ToString(obj)}'";
                            break;
                        case "System.Double":
                            value = Convert.ToDouble(obj).ToString();
                            break;
                        case "System.Decimal":
                            value = Convert.ToDecimal(obj).ToString();
                            break;
                        case "System.DateTime":
                            DateTime dt = Convert.ToDateTime(obj);
                            if (dt == DateTime.MinValue)
                                value = "null";
                            else
                                value = $"'{dt}'";
                            break;
                        case "System.Boolean":
                            value = Convert.ToBoolean(obj).ToString();
                            break;
                    }
                    if (!CheckAttrSqlIgnore(propertyInfo, obj, ref value))
                        continue;
                    if (value == "-1")
                        value = "null";
                    values.Add(value);
                }
                items.Add(values);
            }
            return items;
        }
    }



    public static class SqlExtensions
    {
        /// <summary>
        /// 生成InsertSQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <param name="ignoreFields"></param>
        /// <returns></returns>
        public static string Insert<T>(this T obj, string tableName, List<string> ignoreFields = null)
        {
            Dictionary<string, string> keyValuePairs = obj.GetPropertyValueNotNullForInsert(ignoreFields);
            string sql = $"INSERT INTO {tableName}({string.Join(",", keyValuePairs.Keys)})VALUES({string.Join(",", keyValuePairs.Values)})";
            return sql;
        }

        /// <summary>
        /// 生成UpdateSQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <param name="ignoreFields"></param>
        /// <returns></returns>
        public static string Update<T>(this T obj, string tableName, List<string> ignoreFields = null)
        {
            List<string> items = obj.GetPropertyValueNotNullForUpdate(ignoreFields);
            string sql = $"UPDATE {tableName} SET {string.Join(",", items)}";// where id='{data.ID}'";
            return sql;
        }

        /// <summary>
        /// 生成批量插入SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="tableName"></param>
        /// <param name="ignoreFields"></param>
        /// <returns></returns>
        public static string InsertBatch<T>(this List<T> obj, string tableName, List<string> ignoreFields = null)
        {
            List<string> fields;
            List<List<string>> values = obj.GetPropertyValueForBatchInsert(out fields, ignoreFields);
            List<string> strings = new List<string>();
            foreach (List<string> value in values)
                strings.Add($"({string.Join(",", value)})");

            string sql = $"INSERT INTO {tableName} ({string.Join(",", fields)}) VALUES{string.Join(",", strings)}";
            return sql;
        }
    }
}
